package com.xbongbong.paas.util;

import com.xbongbong.paas.constant.ExcelConstant;
import com.xbongbong.paas.pojo.FieldAttrEntityForImport;
import com.xbongbong.paas.pojo.dto.ImportFormDataDTO;
import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.xml.parsers.ParserConfigurationException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 *
 * @author 魏荣杰
 * @date 2019/11/18 10:01 上午
 * @since v4.6.0
 * @version v4.6.0
 */
public class XlsxProcessUtil {
    private static final Logger LOG = LoggerFactory.getLogger(XlsxProcessUtil.class);

    private ExcelReadDataDelegated excelReadDataDelegated;

    public ExcelReadDataDelegated getExcelReadDataDelegated() {
        return excelReadDataDelegated;
    }

    public void setExcelReadDataDelegated(ExcelReadDataDelegated excelReadDataDelegated) {
        this.excelReadDataDelegated = excelReadDataDelegated;
    }

    public XlsxProcessUtil(ExcelReadDataDelegated excelReadDataDelegated) {
        this.excelReadDataDelegated = excelReadDataDelegated;
    }

    /**
     * 开始读取行数从第0行开始计算, 控制Excel读取起初位置，-1为从第一行读取
     */
    private int rowIndex = 0;
    /**
     * 一行的数据
     */
    private List<String> rowCells = new ArrayList<>();
    /**
     * 表格名称
     */
    private String sheetName;
    /**
     * 表格索引值
     */
    private int sheetIndex = 0;
    /**
     * 读取的行数
     */
    private int totalRows = 0;
    /**
     * 数据开始的行数
     */
    private int startRowIndex = -1;
    /**
     * 标题
     */
    private List<List<String>> titleLists = new ArrayList<>();
    /**
     * 解释
     */
    private List<FieldAttrEntityForImport> fieldAttrEntityList = new ArrayList<>();

    private ImportFormDataDTO importFormDataDTO;

    /**
     * Description:
     * @param filePath
     * @param startRowIndex
     * @param fieldAttrEntityList
     * @author 魏荣杰
     * @date 2019/11/18 3:58 下午
     * @since v4.6.0
     */
    public void processAllSheet(String filePath, int startRowIndex, List<FieldAttrEntityForImport> fieldAttrEntityList) throws Exception {
        if (!Objects.equals(startRowIndex, -1)) {
            this.startRowIndex = startRowIndex;
        }
//        this.titleLists = titleLists;
        this.fieldAttrEntityList = fieldAttrEntityList;
        OPCPackage pkg = OPCPackage.open(filePath, PackageAccess.READ);
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(pkg);
        XSSFReader xssfReader = new XSSFReader(pkg);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        InputStream inputStream = null;
        while (iter.hasNext()) {
            try {
                sheetIndex++;
                inputStream = iter.next();
                sheetName = iter.getSheetName();
                if (sheetName.contains(ExcelConstant.HIDDEN_SHEET)) {
                    continue;
                }
                parserSheetXml(styles, readOnlySharedStringsTable, new XlsxSheetContentsHandler(), inputStream);
            } catch (Exception e) {
                LOG.error("parserSheetXml error: ",e);
            } finally {
                if (Objects.nonNull(inputStream)) {
                    inputStream.close();
                }
            }
        }
        pkg.close();
    }

    /**
     * Description:
     * @param fieldAttrEntityList
     * @param fileInputStream
     * @param startRowIndex
     * @param importFormDataDTO
     * @author 魏荣杰
     * @date 2019/11/18 4:01 下午
     * @since v4.6.0
     */
    public void processAllSheet(InputStream fileInputStream, int startRowIndex, List<FieldAttrEntityForImport> fieldAttrEntityList, ImportFormDataDTO importFormDataDTO) throws Exception {
        if (!Objects.equals(startRowIndex, -1)) {
            this.startRowIndex = startRowIndex;
        }
//        this.titleLists = titleLists;
        this.fieldAttrEntityList = fieldAttrEntityList;
        this.importFormDataDTO = importFormDataDTO;
        OPCPackage pkg = OPCPackage.open(fileInputStream);
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(pkg);
        XSSFReader xssfReader = new XSSFReader(pkg);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        InputStream inputStream = null;
        while (iter.hasNext()) {
            try {
                sheetIndex++;
                inputStream = iter.next();
                sheetName = iter.getSheetName();
                if (sheetName.contains(ExcelConstant.HIDDEN_SHEET)) {
                    continue;
                }
                parserSheetXml(styles, readOnlySharedStringsTable, new XlsxSheetContentsHandler(), inputStream);
            } catch (Exception e) {
                LOG.error("parserSheetXml error: ",e);
            } finally {
                if (Objects.nonNull(inputStream)) {
                    inputStream.close();
                }
            }
        }
        pkg.close();
    }


    /**
     * Description:解析excel 转换成xml
     * @param styles
     * @param readOnlySharedStringsTable
     * @param xlsxSheetContentsHandler
     * @param inputStream
     * @return void
     * @throws
     * @author 魏荣杰
     * @date 2019/11/18 11:19 上午
     * @since
     */
    private void parserSheetXml(StylesTable styles, ReadOnlySharedStringsTable readOnlySharedStringsTable, XlsxSheetContentsHandler xlsxSheetContentsHandler, InputStream inputStream) {
        DataFormatter formatter = new DataFormatter();
        InputSource sheetSource = new InputSource(inputStream);
        try {
            XMLReader sheetParser = SAXHelper.newXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(styles, null, readOnlySharedStringsTable, xlsxSheetContentsHandler, formatter, false);
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
        } catch (ParserConfigurationException | SAXException | IOException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e);
        }

    }


    public class XlsxSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

        /**
         * 是否是一行的第一个单元格
         */
        private boolean firstCellOfRow = false;
        /**
         * 当前行
         */
        private int currentRowNumber = -1;
        /**
         * 当前列
         */
        private int currentColNumber = -1;

        @Override
        public void startRow(int rowNum) {
            firstCellOfRow = true;
            currentRowNumber = rowNum;
            currentColNumber = -1;

        }

        @Override
        public void endRow(int rowNum) {
            totalRows++;
            // 获取表头
            if (currentRowNumber <= startRowIndex && currentRowNumber > rowIndex) {
                List<String> titles = new ArrayList<>(rowCells);
                titleLists.add(titles);
            }
            // 处理数据
            if (currentRowNumber > startRowIndex) {
                // TODO: 2019/11/19 数据表头检验异常以及其他异常的抛出处理
                try {
                    excelReadDataDelegated.readExcelData(sheetName, sheetIndex, totalRows, currentRowNumber, rowCells, titleLists, fieldAttrEntityList, importFormDataDTO);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            rowCells.clear();
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            if (firstCellOfRow) {
                firstCellOfRow = false;
            }
            if (cellReference == null) {
                cellReference = new CellAddress(currentRowNumber, currentColNumber).formatAsString();
            }
            int thisCol = (new CellReference(cellReference)).getCol();
            int missedCols = thisCol - currentColNumber - 1;
            for (int i = 0; i < missedCols; i++) {
                rowCells.add("");
            }
            currentColNumber = thisCol;
            rowCells.add(formattedValue);
        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
        }

    }
}
